﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class SettlementDetailsServices
    {
        private static SettlementDetails GetSettlementDetailsFromDataReader(IDataReader dataReader)
        {
            SettlementDetails retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new SettlementDetails();

                DateTime createdDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["ADDED_DATE"].ToString(), out createdDate))
                {
                    retVal.AddedDate = createdDate;
                }

                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();
                retVal.TotalDue = double.Parse(dataReader["TOTAL_DUE"].ToString());
                retVal.Balance = double.Parse(dataReader["BALANCE"].ToString());
                retVal.AgentCode = dataReader["AGENT_CODE"].ToString();
            }
            return retVal;
        }

        //public static Agent GetAgentByCode(string code)
        //{
        //    Agent retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select * from agent where code = '" + code + "'";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            if (dataReader.Read())
        //            {
        //                retVal = GetAgentFromDataReader(dataReader);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static SettlementDetailsCollection GetSettlementDetailsCollection()
        {
            SettlementDetailsCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SETTLEMENT_DETAILS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new SettlementDetailsCollection();
                    while (dataReader.Read())
                    {
                        SettlementDetails item = GetSettlementDetailsFromDataReader(dataReader);
                        if (item != null)
                        {
                            retVal.Add(item);
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetSettlementDetailsCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM SETTLEMENT_DETAILS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(SettlementDetails settlementDetail)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO SETTLEMENT_DETAILS(ADDED_DATE, CLIENT_CODE, TOTAL_DUE, BALANCE, AGENT_CODE)" +
                                  "values(:v1, :v2, :v3, :v4, :v5)";

                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = settlementDetail.AddedDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = settlementDetail.ClientCode;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = settlementDetail.TotalDue;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = settlementDetail.Balance;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = settlementDetail.AgentCode;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        //public static void Delete(string code)
        //{
        //    OracleConnection con = new OracleConnection();
        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "delete from agent where code = '" + code + "'";
        //        cmd.Connection = con;

        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        //public static void Update(Agent agent)
        //{
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "update agent set " +
        //                          "NAME = :v2 ," +
        //                          "ADDRESS_1 = :v3 ," +
        //                          "ADDRESS_2 = :v4 ," +
        //                          "ADDRESS_3 = :v5 ," +
        //                          "ADDRESS_4 = :v6 ," +
        //                          "TELEPHONE_1 = :v7 ," +
        //                          "TELEPHONE_2 = :v8 ," +
        //                          "FAX = :v9 ," +
        //                          "CREATED_DATE = :v10, INTEREST_RATE = :v11 where CODE = :v1";
        //        cmd.Parameters.Add(":v1", OracleType.VarChar).Value = agent.Code;
        //        cmd.Parameters.Add(":v2", OracleType.VarChar).Value = agent.Name;
        //        cmd.Parameters.Add(":v3", OracleType.VarChar).Value = agent.Address_1;
        //        cmd.Parameters.Add(":v4", OracleType.VarChar).Value = agent.Address_2;
        //        cmd.Parameters.Add(":v5", OracleType.VarChar).Value = agent.Address_3;
        //        cmd.Parameters.Add(":v6", OracleType.VarChar).Value = agent.Address_4;
        //        cmd.Parameters.Add(":v7", OracleType.VarChar).Value = agent.Telephone1;
        //        cmd.Parameters.Add(":v8", OracleType.VarChar).Value = agent.Telephone1;
        //        cmd.Parameters.Add(":v9", OracleType.VarChar).Value = agent.Fax;
        //        cmd.Parameters.Add(":v10", OracleType.DateTime).Value = agent.CreatedDate;
        //        cmd.Parameters.Add(":v11", OracleType.Number).Value = agent.Interest_Rate;

        //        cmd.Connection = con;
        //        cmd.ExecuteNonQuery();

        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}
    }
}
